package com.carreirati.evaluation.domain;

import java.util.List;

import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.jpa.activerecord.RooJpaActiveRecord;
import org.springframework.roo.addon.json.RooJson;
import org.springframework.roo.addon.tostring.RooToString;

@RooJavaBean
@RooToString
@RooJpaActiveRecord(identifierType = VwGetEvaluationResultsByKnowledgeAreaPK.class, versionField = "", table = "vw_get_evaluation_results_by_knowledge_area", schema = "dbo")
@RooDbManaged(automaticallyDelete = true)
@RooJson
public class VwGetEvaluationResultsByKnowledgeArea {
 
	public static List<VwGetEvaluationResultsByKnowledgeArea> findAllVwGetEvaluationResultsByKnowledgeArea(long knowledgeAreaId) {
		 return entityManager().createNativeQuery("SELECT * FROM vw_get_evaluation_results_by_knowledge_area o WHERE knowledge_area in (SELECT description FROM knowledge_area where ID = '"+knowledgeAreaId+"')  ORDER BY knowledge_area", VwGetEvaluationResultsByKnowledgeArea.class).getResultList();

	}
	
	
	public static List<VwGetEvaluationResultsByKnowledgeArea> getProfileQueryByKnowledgeArea(String profileId){
		
		String sqlString = "SELECT * FROM vw_get_evaluation_results_by_knowledge_area_expected_grade ";
			   sqlString += "WHERE profile_id = '" +profileId + "'";		
		
	    return entityManager().createNativeQuery(sqlString, VwGetEvaluationResultsByKnowledgeArea.class).getResultList();

	}

	public static List<VwGetEvaluationResultsByKnowledgeArea> findAllVwGetEvaluationResultsByKnowledgeArea(String params, String profileId) {
				
		String[] aux = params.split("_");
		String sqlQueryAux = "";
		String sqlQueryAux2 = "";
		for(int i=2;i<aux.length;i++){
			String[] result = aux[i].split(",");
			if (i ==2){
				sqlQueryAux = "  SELECT user_id  + profile_id  + (select description from knowledge_area where id =  knowledge_area_id) FROM vw_get_evaluation_results_by_knowledge_area_aux ";  
				sqlQueryAux += " WHERE knowledge_area_id  =  '" + result[0] +"' and (grade < '"+ result[1] +"' or grade > '"+ result[2] +"') ";

				sqlQueryAux2 = " SELECT user_id , profile_id , knowledge_area_id FROM vw_get_evaluation_results_by_knowledge_area_aux  WHERE  ";  
				sqlQueryAux2 += "   knowledge_area_id  =  '" + result[0] +"' and (grade < '"+ result[1] +"' or grade > '"+ result[2] +"') ";

				
			}	else {
				sqlQueryAux += " UNION SELECT user_id  + profile_id  + (select description from knowledge_area where id =  knowledge_area_id) FROM vw_get_evaluation_results_by_knowledge_area_aux "; 
				sqlQueryAux += "WHERE knowledge_area_id  =  '" + result[0] +"' and (grade < '"+ result[1] +"' or grade > '"+ result[2] +"') ";
				
				sqlQueryAux2 += "UNION SELECT user_id , profile_id , knowledge_area_id FROM vw_get_evaluation_results_by_knowledge_area_aux  WHERE  ";  
				sqlQueryAux2 += " knowledge_area_id  =  '" + result[0] +"' and (grade < '"+ result[1] +"' or grade > '"+ result[2] +"') ";
			}

		}
			
		String sqlQuery = "SELECT * FROM vw_get_evaluation_results_by_knowledge_area  WHERE profile_id = '"+ profileId +"' AND (USER_ID + profile_id + knowledge_area) NOT IN ( " + sqlQueryAux + ")"     ;
		sqlQuery += " AND (user_Id+profile_id) not in (SELECT USER_ID +profile_id FROM (" + sqlQueryAux2 + ") x GROUP BY USER_ID, profile_id HAVING count(*)  != (SELECT COUNT(*) FROM knowledge_area))";
	    sqlQuery += " AND convert(VARCHAR(10),appraisal_From,112) >= '" + aux[0].replaceAll("-","")+ "' AND convert(VARCHAR(10),appraisal_From,112) <= '" + aux[1].replaceAll("-","") +"'";
		
		// sqlQuery += " UNION " + VwGetEvaluationResultsByKnowledgeArea.getProfileQueryByKnowledgeArea(profileId);
		return entityManager().createNativeQuery(sqlQuery, VwGetEvaluationResultsByKnowledgeArea.class).getResultList();

	}
}
